package com.css.fxfzypg.modules.saresults.repository.impl;

import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.modules.saresults.entity.*;
import com.css.fxfzypg.modules.saresults.param.DrillHoleQueryParam;
import com.css.fxfzypg.modules.saresults.repository.DrillHoleQueryRepository;
import com.css.fxfzypg.modules.saresults.vo.DrillHoleQueryVo;
import com.css.fxfzypg.modules.saresults.vo.MapVo;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.stream.Collectors;

@Repository
public class DrillHoleQueryRepositoryImpl implements DrillHoleQueryRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public HashMap<String, Object> queryData(DrillHoleQueryParam param, int page, int size,List<String> roleList,String geom) {
//        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT ( dpf.borehole_number), dmp.project_id, dmp.model, dpf.project_name, dpf.hl_longitude, dpf.hl_latitude, dpf.ground_elevation, dpf.drilling_depth, dsc.field_type, y06_exist, y08_exist, dmp.create_time, dmp.is_delete FROM ( SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_ap_basic_map UNION ALL SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_qp_basic_map UNION ALL SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_xqh_basic_map ) dmp LEFT JOIN dzqh_ap_crux_layer_pf dpf ON dmp.project_id = dpf.project_id LEFT JOIN dzqh_ap_crux_sd dsd ON dpf.borehole_number = dsd.borehole_number LEFT JOIN dzqh_ap_crux_sc dsc ON dpf.borehole_number = dsc.borehole_number LEFT JOIN dzqh_ap_crux_dh ddh ON dsd.st_number = ddh.st_number UNION ALL SELECT drill_synthesize_code AS borehole_number, ID AS pf_id, data_type AS model, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, y06_exist, y08_exist, create_time, is_delete FROM dzqh_jcsj_hl ) result_data WHERE is_delete = '0'");
        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT pf.project_id,pf.name, ( pf.borehole_number), pf.pf_id, pf.project_name, pf.hl_longitude, pf.hl_latitude, pf.ground_elevation, pf.drilling_depth, sc.field_type, pf.data_type AS model, '1' AS y06_exist, '1' AS y08_exist, create_time FROM dzqh_ap_crux_layer_pf pf LEFT JOIN dzqh_ap_crux_sc sc ON pf.pf_id = sc.pf_id WHERE pf.is_delete = '0' UNION ALL SELECT extends1 as project_id,drill_depend_project AS name, drill_synthesize_code AS borehole_number, ID AS pf_id, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, data_type AS model, y06_exist, y08_exist, create_time FROM dzqh_jcsj_hl ) hl WHERE 1 = 1 ");
        //StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT pf.project_id, ( pf.borehole_number), pf.pf_id, pf.project_name, pf.hl_longitude, pf.hl_latitude, pf.ground_elevation, pf.drilling_depth, sc.field_type, pf.data_type AS model, '1' AS y06_exist, '1' AS y08_exist, create_time FROM dzqh_ap_crux_layer_pf pf LEFT JOIN dzqh_ap_crux_sc sc ON pf.pf_id = sc.pf_id WHERE pf.is_delete = '0' UNION ALL SELECT extends1 as project_id, drill_synthesize_code AS borehole_number, ID AS pf_id, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, data_type AS model, y06_exist, y08_exist, create_time FROM dzqh_jcsj_hl ) hl WHERE hl_longitude is not null and hl_latitude is not null and hl_longitude!='' and hl_latitude!='' and ST_Intersects(st_geometryfromtext('POINT(' || hl_longitude || ' ' || hl_latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))");
        if (PlatformObjectUtils.isNotEmpty(geom)) {
            sql.append(" and hl_longitude is not null and hl_latitude is not null and hl_longitude!='' and hl_latitude!='' and ST_Intersects(st_geometryfromtext('POINT(' || hl_longitude || ' ' || hl_latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))");
        }

//        if(!roleList.contains(FxfzConstants.DZQH_GL_ROLE)){
//            sql.append(" and model != '普查数据'");
//        }
        if (PlatformObjectUtils.isNotEmpty(param.getProjectName())) {
            sql.append(" and project_name like '%" + param.getProjectName() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLongitude())) {
            sql.append(" and hl_longitude >= '" + param.getStartHlLongitude()+"'");
        }
        if(PlatformObjectUtils.isNotEmpty(param.getEndHlLongitude())){
            sql.append(" and hl_longitude <= '" + param.getEndHlLongitude()+"'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLatitude())) {
            sql.append(" and hl_latitude >= '" + param.getStartHlLatitude() + "'");
        }
        if(PlatformObjectUtils.isNotEmpty(param.getEndHlLatitude())){
            sql.append(" and hl_latitude <= '" + param.getEndHlLatitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataType())) {
            sql.append(" and model = '" + param.getDataType() + "'");
        }
//        if (PlatformObjectUtils.isNotEmpty(param.getSoilDynamic())) {
//            sql.append(" and y06_exist = '" + param.getSoilDynamic() + "'");
//        }
//        if (PlatformObjectUtils.isNotEmpty(param.getMeasuredValues())) {
//            sql.append(" and y08_exist = '" + param.getMeasuredValues() + "'");
//        }

        if (PlatformObjectUtils.isNotEmpty(param.getSiteType())) {
            sql.append(" and field_type = '" + param.getSiteType() + "'");
        }

        //Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DrillHoleQueryVo.class)).size();
//        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
//        sql.append(orderSql).append(limitSql);
        sql.append(orderSql);
        List<DrillHoleQueryVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DrillHoleQueryVo.class));
        List<DrillHoleQueryVo> result = new ArrayList<>(list.size());
        list.parallelStream().forEach(it -> {
            if (!it.getModel().equals(FxfzConstants.DZQH_JCSJ_MODEL)) {
                Integer status = getY06ExistStatus(it.getPfId(), it.getProjectId());
                if (status != 0) {
                    it.setY06Exist("1");
                } else {
                    it.setY06Exist("0");
                }
                Integer status1 = getY08ExistStatus(it.getPfId(), it.getProjectId());
                if (status1 != 0) {
                    it.setY08Exist("1");
                } else {
                    it.setY08Exist("0");
                }
                if(StringUtils.isNotBlank(param.getSoilDynamic()) && StringUtils.isNotBlank(param.getMeasuredValues())){
                    if(it.getY06Exist().equals(param.getSoilDynamic()) && it.getY08Exist().equals(param.getMeasuredValues())){
                        result.add(it);
                    }
                }else if(StringUtils.isNotBlank(param.getSoilDynamic()) && StringUtils.isBlank(param.getMeasuredValues())){
                    if(it.getY06Exist().equals(param.getSoilDynamic())){
                        result.add(it);
                    }
                }else if(StringUtils.isBlank(param.getSoilDynamic()) && StringUtils.isNotBlank(param.getMeasuredValues())){
                    if(it.getY08Exist().equals(param.getMeasuredValues())){
                        result.add(it);
                    }
                }else{
                    result.add(it);
                }
            }else{
                result.add(it);
            }
        });
        List<DrillHoleQueryVo> drillHoleQueryVos = new ArrayList<>();
        if(result.size()>0){
            int startIndex = (page - 1) * size;
            int endIndex = Math.min(startIndex + size, result.size());
            drillHoleQueryVos = result.subList(startIndex, endIndex);
        }
        HashMap<String, Object> resultMap = new HashMap();
//        resultMap.put("total", total);
//        resultMap.put("rows", list);
        resultMap.put("total", result.size());
        resultMap.put("rows", drillHoleQueryVos);
        return resultMap;
    }

    @Override
    public List<MapVo> queryMapData(DrillHoleQueryParam param, List<String> roleList) {
//        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT ( dpf.borehole_number), dmp.project_id, dmp.model, dpf.project_name, dpf.hl_longitude, dpf.hl_latitude, dpf.ground_elevation, dpf.drilling_depth, dsc.field_type, y06_exist, y08_exist, dmp.create_time, dmp.is_delete FROM ( SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_ap_basic_map UNION ALL SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_qp_basic_map UNION ALL SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_xqh_basic_map ) dmp LEFT JOIN dzqh_ap_crux_layer_pf dpf ON dmp.project_id = dpf.project_id LEFT JOIN dzqh_ap_crux_sd dsd ON dpf.borehole_number = dsd.borehole_number LEFT JOIN dzqh_ap_crux_sc dsc ON dpf.borehole_number = dsc.borehole_number LEFT JOIN dzqh_ap_crux_dh ddh ON dsd.st_number = ddh.st_number UNION ALL SELECT drill_synthesize_code AS borehole_number, ID AS pf_id, data_type AS model, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, y06_exist, y08_exist, create_time, is_delete FROM dzqh_jcsj_hl ) result_data WHERE is_delete = '0'");
        StringBuilder sql = new StringBuilder("SELECT pf_id,hl_longitude,hl_latitude,model FROM( SELECT DISTINCT pf.project_id, ( pf.borehole_number), pf.pf_id, pf.project_name, pf.hl_longitude, pf.hl_latitude, pf.ground_elevation, pf.drilling_depth, sc.field_type, pf.data_type AS model, '1' AS y06_exist, '1' AS y08_exist, create_time FROM dzqh_ap_crux_layer_pf pf LEFT JOIN dzqh_ap_crux_sc sc ON pf.pf_id = sc.pf_id WHERE pf.is_delete = '0' UNION ALL SELECT extends1 as project_id, drill_synthesize_code AS borehole_number, ID AS pf_id, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, data_type AS model, y06_exist, y08_exist, create_time FROM dzqh_jcsj_hl ) hl WHERE 1 = 1 ");
        if(!roleList.contains(FxfzConstants.DZQH_GL_ROLE)){
            sql.append(" and model != '普查数据'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProjectName())) {
            sql.append(" and project_name like '%" + param.getProjectName() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLongitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLongitude())) {
            sql.append(" and hl_longitude between '" + param.getStartHlLongitude() + "' and '" + param.getEndHlLongitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLatitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLatitude())) {
            sql.append(" and hl_latitude between '" + param.getStartHlLatitude() + "' and '" + param.getEndHlLatitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataType())) {
            sql.append(" and model = '" + param.getDataType() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getSoilDynamic())) {
            sql.append(" and y06_exist = '" + param.getSoilDynamic() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getMeasuredValues())) {
            sql.append(" and y08_exist = '" + param.getMeasuredValues() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getSiteType())) {
            sql.append(" and field_type = '" + param.getSiteType() + "'");
        }

        List<MapVo> list= jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MapVo.class));
        return list;

    }

    public Integer getY08ExistStatus(String pfId, String projectId) {
        String sql = "";
        if (projectId != null) {
            sql = "select count(1) from dzqh_ap_crux_dh where project_id = '" + projectId + "'";
        } else {
            sql = "select count(1) from dzqh_ap_crux_dh where pf_id = '" + pfId + "'";
        }
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public String queryMaxCode() {
        try {
            String sql = "select id from dzqh_jcsj_hl order by id desc limit 1";
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (DataAccessException e) {
            return null;
        }
    }

    public Integer getY06ExistStatus(String pfId, String projectId) {
        String sql = "";
        if (projectId != null) {
            sql = "select count(*) from dzqh_ap_crux_sd where project_id = '" + projectId + "'";
        } else {
            sql = "select count(*) from dzqh_ap_crux_sd where pf_id = '" + pfId + "'";
        }
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public void saveHlByJcsj(DzqhykzsfglzxtDrillEntity it) {
        jdbcTemplate.update("insert into dzqh_jcsj_hl  (id,drill_depend_project,longitude,latitude,drill_synthesize_code,elevation,drill_depth,y06_exist,y08_exist,site_type,data_version,data_type,is_delete,create_time,province ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )",
                it.getId(), it.getDrillDependProject(), it.getLongitude(), it.getLatitude(), it.getDrillSynthesizeCode(), it.getElevation(), it.getDrillDepth(), it.getY06Exist(), it.getY08Exist(), it.getSiteType(), it.getDataVersion(), it.getDataType(), it.getIsDelete(), it.getCreateTime(),it.getProvince());
    }

    @Override
    public List<DzqhApCruxLayerPfEntity> getPfEntityById(String id) {
        try {
            String sql = "select * from dzqh_ap_crux_layer_pf where pf_id = '" + id + "' and is_delete = '0'";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public DzqhApCruxScEntity getScEntityByNumber(String boreholeNumber, String projectId) {
        try {
            String sql = "select * from dzqh_ap_crux_sc where borehole_number = '" + boreholeNumber + "' and pf_id = '" + projectId + "'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxScEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<DzqhApCruxSdEntity> getSdListByNumber(String boreholeNumber, String projectId) {
        String sql = "select * from dzqh_ap_crux_sd where borehole_number = '" + boreholeNumber + "'and pf_id = '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxSdEntity.class));
    }

    @Override
    public DzqhApCruxDhEntity getDhListByCode(String number, String projectId) {
        try {
            String sql = "select * from dzqh_ap_crux_dh where st_number = '" + number + "' and pf_id = '" + projectId + "'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxDhEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<DrillHoleQueryVo> getData(DrillHoleQueryParam param) {
//        StringBuilder sql = new StringBuilder("SELECT DISTINCT( dpf.borehole_number), dmp.project_id, dmp.model, dmp.project_name, dpf.hl_longitude, dpf.hl_latitude, dpf.ground_elevation, dpf.drilling_depth, dsc.field_type, y06_exist, y08_exist, dmp.is_delete FROM ( SELECT project_id, project_name, model, '1' as y06_exist, '1' as y08_exist, is_delete FROM dzqh_ap_basic_map UNION ALL SELECT project_id, project_name, model, '1' as y06_exist, '1' as y08_exist, is_delete FROM dzqh_qp_basic_map UNION ALL SELECT project_id, project_name, model, '1' as y06_exist, '1' as y08_exist, is_delete FROM dzqh_xqh_basic_map ) dmp LEFT JOIN dzqh_ap_crux_layer_pf dpf ON dmp.project_id = dpf.project_id LEFT JOIN dzqh_ap_crux_sd dsd ON dpf.borehole_number = dsd.borehole_number LEFT JOIN dzqh_ap_crux_sc dsc ON dpf.borehole_number = dsc.borehole_number LEFT JOIN dzqh_ap_crux_dh ddh ON dsd.st_number = ddh.st_number UNION ALL SELECT drill_synthesize_code AS borehole_number, ID AS pf_id, data_type AS model, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, y06_exist, y08_exist, is_delete FROM dzqh_jcsj_hl WHERE is_delete = '0'");
        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT pf.project_id,pf.name, ( pf.borehole_number), pf.pf_id, pf.project_name, pf.hl_longitude, pf.hl_latitude, pf.ground_elevation, pf.drilling_depth, sc.field_type, pf.data_type AS model, '1' AS y06_exist, '1' AS y08_exist, create_time FROM dzqh_ap_crux_layer_pf pf LEFT JOIN dzqh_ap_crux_sc sc ON pf.pf_id = sc.pf_id WHERE pf.is_delete = '0' UNION ALL SELECT extends1 as project_id,drill_depend_project as name, drill_synthesize_code AS borehole_number, ID AS pf_id, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, data_type AS model, y06_exist, y08_exist, create_time FROM dzqh_jcsj_hl ) hl WHERE 1 = 1 ");
        if(PlatformObjectUtils.isNotEmpty(param.getIds())){
            String result = param.getIds().stream()
                    .map(s -> "'" + s + "'")
                    .collect(Collectors.joining(", "));
            sql.append(" and project_id in ("+result+")");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProjectName())) {
            sql.append(" and project_name like '%" + param.getProjectName() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLongitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLongitude())) {
            sql.append(" and hl_longitude between " + param.getStartHlLongitude() + " and " + param.getEndHlLongitude());
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLatitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLatitude())) {
            sql.append(" and hl_latitude between " + param.getStartHlLatitude() + " and " + param.getEndHlLatitude());
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataType())) {
            sql.append(" and model = '" + param.getDataType() + "'");
        }
//        if (PlatformObjectUtils.isNotEmpty(param.getSoilDynamic())) {
//            sql.append(" and y06_exist = '" + param.getSoilDynamic() + "'");
//        }
//        if (PlatformObjectUtils.isNotEmpty(param.getMeasuredValues())) {
//            sql.append(" and y08_exist = '" + param.getMeasuredValues() + "'");
//        }
        if (PlatformObjectUtils.isNotEmpty(param.getSiteType())) {
            sql.append(" and field_type = '" + param.getSiteType() + "'");
        }
        List<DrillHoleQueryVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DrillHoleQueryVo.class));
        List<DrillHoleQueryVo> result = new ArrayList<>(list.size());
        list.parallelStream().forEach(it -> {
            if (!it.getModel().equals(FxfzConstants.DZQH_JCSJ_MODEL)) {
                Integer status = getY06ExistStatus(it.getPfId(), it.getProjectId());
                if (status != 0) {
                    it.setY06Exist("1");
                } else {
                    it.setY06Exist("0");
                }
                Integer status1 = getY08ExistStatus(it.getPfId(), it.getProjectId());
                if (status1 != 0) {
                    it.setY08Exist("1");
                } else {
                    it.setY08Exist("0");
                }
                if(StringUtils.isNotBlank(param.getSoilDynamic()) && StringUtils.isNotBlank(param.getMeasuredValues())){
                    if(it.getY06Exist().equals(param.getSoilDynamic()) && it.getY08Exist().equals(param.getMeasuredValues())){
                        result.add(it);
                    }
                }else if(StringUtils.isNotBlank(param.getSoilDynamic()) && StringUtils.isBlank(param.getMeasuredValues())){
                    if(it.getY06Exist().equals(param.getSoilDynamic())){
                        result.add(it);
                    }
                }else if(StringUtils.isBlank(param.getSoilDynamic()) && StringUtils.isNotBlank(param.getMeasuredValues())){
                    if(it.getY08Exist().equals(param.getMeasuredValues())){
                        result.add(it);
                    }
                }else{
                    result.add(it);
                }
            }else{
                result.add(it);
            }
        });
//
//        list.parallelStream().forEach(it -> {
//            if (!it.getModel().equals(FxfzConstants.DZQH_JCSJ_MODEL)) {
//                Integer status = getY06ExistStatus(it.getPfId(), it.getProjectId());
//                if (status != 0) {
//                    it.setY06Exist("1");
//                } else {
//                    it.setY06Exist("0");
//                }
//                Integer status1 = getY08ExistStatus(it.getPfId(), it.getProjectId());
//                if (status1 != 0) {
//                    it.setY08Exist("1");
//                } else {
//                    it.setY08Exist("0");
//                }
//            }
//        });
        return result;
    }

    @Override
    public List<DzqhApCruxLayerPfEntity> getPfEntityByProjectId(String id) {
        try {
            String sql = "select * from dzqh_ap_crux_layer_pf where project_id = '" + id + "' and is_delete = '0'";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public DzqhApCruxScEntity getScEntityByProjectId(String projectId, String boreholeNumber) {
        try {
            String sql = "select * from dzqh_ap_crux_sc where borehole_number = '" + boreholeNumber + "' and project_id = '" + projectId + "'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxScEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<DzqhApCruxSdEntity> getSdListByProjectId(String projectId, String boreholeNumber) {
        String sql = "select * from dzqh_ap_crux_sd where borehole_number = '" + boreholeNumber + "'and project_id = '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxSdEntity.class));
    }

    @Override
    public DzqhApCruxDhEntity getDhListByProjectId(String stNumber, String projectId) {
        try {
            String sql = "select * from dzqh_ap_crux_dh where st_number = '" + stNumber + "' and project_id = '" + projectId + "'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxDhEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    /*@Override
    public List<MapVo> getByGeom(DrillHoleQueryParam param,String geom) {
        //String sql = "SELECT * FROM( SELECT DISTINCT pf.pf_id, pf.hl_longitude, pf.hl_latitude, pf.data_type AS model FROM dzqh_ap_crux_layer_pf pf WHERE pf.is_delete = '0' UNION ALL SELECT ID AS pf_id, longitude AS hl_longitude, latitude AS hl_latitude, data_type AS model FROM dzqh_jcsj_hl) hl   WHERE hl_longitude is not null and hl_latitude is not null and hl_longitude!='' and hl_latitude!='' and ST_Intersects(st_geometryfromtext('POINT(' || hl_longitude || ' ' || hl_latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))";

        List<MapVo> list= jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(MapVo.class));
        return list;
        //return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(MapVo.class));
    }*/

    @Override
    public List<MapVo> getByGeom(DrillHoleQueryParam param, List<String> roleList,String geom) {
//        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT ( dpf.borehole_number), dmp.project_id, dmp.model, dpf.project_name, dpf.hl_longitude, dpf.hl_latitude, dpf.ground_elevation, dpf.drilling_depth, dsc.field_type, y06_exist, y08_exist, dmp.create_time, dmp.is_delete FROM ( SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_ap_basic_map UNION ALL SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_qp_basic_map UNION ALL SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_xqh_basic_map ) dmp LEFT JOIN dzqh_ap_crux_layer_pf dpf ON dmp.project_id = dpf.project_id LEFT JOIN dzqh_ap_crux_sd dsd ON dpf.borehole_number = dsd.borehole_number LEFT JOIN dzqh_ap_crux_sc dsc ON dpf.borehole_number = dsc.borehole_number LEFT JOIN dzqh_ap_crux_dh ddh ON dsd.st_number = ddh.st_number UNION ALL SELECT drill_synthesize_code AS borehole_number, ID AS pf_id, data_type AS model, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, y06_exist, y08_exist, create_time, is_delete FROM dzqh_jcsj_hl ) result_data WHERE is_delete = '0'");
        StringBuilder sql = new StringBuilder("SELECT pf_id,hl_longitude,hl_latitude,model FROM( SELECT DISTINCT pf.project_id, ( pf.borehole_number), pf.pf_id, pf.project_name, pf.hl_longitude, pf.hl_latitude, pf.ground_elevation, pf.drilling_depth, sc.field_type, pf.data_type AS model, '1' AS y06_exist, '1' AS y08_exist, create_time FROM dzqh_ap_crux_layer_pf pf LEFT JOIN dzqh_ap_crux_sc sc ON pf.pf_id = sc.pf_id WHERE pf.is_delete = '0' UNION ALL SELECT extends1 as project_id, drill_synthesize_code AS borehole_number, ID AS pf_id, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, data_type AS model, y06_exist, y08_exist, create_time FROM dzqh_jcsj_hl ) hl WHERE 1 = 1 ");
        if (PlatformObjectUtils.isNotEmpty(geom)) {
            sql.append(" and hl_longitude is not null and hl_latitude is not null and hl_longitude!='' and hl_latitude!='' and ST_Intersects(st_geometryfromtext('POINT(' || hl_longitude || ' ' || hl_latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))");
        }

        if(!roleList.contains(FxfzConstants.DZQH_GL_ROLE)){
            sql.append(" and model != '普查数据'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProjectName())) {
            sql.append(" and project_name like '%" + param.getProjectName() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLongitude())) {
            sql.append(" and hl_longitude >= '" + param.getStartHlLongitude() +"'");
        }
        if( PlatformObjectUtils.isNotEmpty(param.getEndHlLongitude())){
            sql.append(" and hl_longitude <= '" + param.getEndHlLongitude() +"'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLatitude())) {
            sql.append(" and hl_latitude >= '" + param.getStartHlLatitude() + "'");
        }
        if(PlatformObjectUtils.isNotEmpty(param.getEndHlLatitude())){
            sql.append(" and hl_latitude <= '" + param.getEndHlLatitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataType())) {
            sql.append(" and model = '" + param.getDataType() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getSoilDynamic())) {
            sql.append(" and y06_exist = '" + param.getSoilDynamic() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getMeasuredValues())) {
            sql.append(" and y08_exist = '" + param.getMeasuredValues() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getSiteType())) {
            sql.append(" and field_type = '" + param.getSiteType() + "'");
        }

        List<MapVo> list= jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MapVo.class));
        return list;

    }

    @Override
    public HashMap<String, Object> getListByGeom(String geom,int page, int size,List<String> roleList) {
//        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT ( dpf.borehole_number), dmp.project_id, dmp.model, dpf.project_name, dpf.hl_longitude, dpf.hl_latitude, dpf.ground_elevation, dpf.drilling_depth, dsc.field_type, y06_exist, y08_exist, dmp.create_time, dmp.is_delete FROM ( SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_ap_basic_map UNION ALL SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_qp_basic_map UNION ALL SELECT project_id, model, '1' AS y06_exist, '1' AS y08_exist, create_time, is_delete FROM dzqh_xqh_basic_map ) dmp LEFT JOIN dzqh_ap_crux_layer_pf dpf ON dmp.project_id = dpf.project_id LEFT JOIN dzqh_ap_crux_sd dsd ON dpf.borehole_number = dsd.borehole_number LEFT JOIN dzqh_ap_crux_sc dsc ON dpf.borehole_number = dsc.borehole_number LEFT JOIN dzqh_ap_crux_dh ddh ON dsd.st_number = ddh.st_number UNION ALL SELECT drill_synthesize_code AS borehole_number, ID AS pf_id, data_type AS model, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, y06_exist, y08_exist, create_time, is_delete FROM dzqh_jcsj_hl ) result_data WHERE is_delete = '0'");
        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT pf.project_id, ( pf.borehole_number), pf.pf_id, pf.project_name, pf.hl_longitude, pf.hl_latitude, pf.ground_elevation, pf.drilling_depth, sc.field_type, pf.data_type AS model, '1' AS y06_exist, '1' AS y08_exist, create_time FROM dzqh_ap_crux_layer_pf pf LEFT JOIN dzqh_ap_crux_sc sc ON pf.pf_id = sc.pf_id WHERE pf.is_delete = '0' UNION ALL SELECT extends1 as project_id, drill_synthesize_code AS borehole_number, ID AS pf_id, drill_depend_project AS project_name, longitude AS hl_longitude, latitude AS hl_latitude, elevation AS ground_elevation, drill_depth AS drilling_depth, site_type AS field_type, data_type AS model, y06_exist, y08_exist, create_time FROM dzqh_jcsj_hl ) hl WHERE hl_longitude is not null and hl_latitude is not null and hl_longitude!='' and hl_latitude!='' and ST_Intersects(st_geometryfromtext('POINT(' || hl_longitude || ' ' || hl_latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))");
        if(!roleList.contains(FxfzConstants.DZQH_GL_ROLE)){
            sql.append(" and model != '普查数据'");
        }

        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DrillHoleQueryVo.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        sql.append(orderSql).append(limitSql);
        List<DrillHoleQueryVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DrillHoleQueryVo.class));

        list.parallelStream().forEach(it -> {
            if (!it.getModel().equals(FxfzConstants.DZQH_JCSJ_MODEL)) {
                Integer status = getY06ExistStatus(it.getPfId(), it.getProjectId());
                if (status != 0) {
                    it.setY06Exist("1");
                } else {
                    it.setY06Exist("0");
                }
                Integer status1 = getY08ExistStatus(it.getPfId(), it.getProjectId());
                if (status1 != 0) {
                    it.setY08Exist("1");
                } else {
                    it.setY08Exist("0");
                }
            }
        });
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", list);
        return resultMap;
    }
}
